﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace StreetSmartAdmin
{
    /// <summary>
    /// </summary>
    public class SqlDbHelper
    {
        private string connectionString;
        /// <summary>
        /// 
        /// </summary>
        public string ConnectionString
        {
            set { connectionString = value; }
        }
        /// <summary>
        /// 
        /// </summary>
        public SqlDbHelper()
            //: this(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString)
        {
            this.connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SQLServer2005DBConnectionString"].ConnectionString;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        public SqlDbHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, CommandType.Text, null);
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, CommandType commandType)
        {
            return ExecuteDataTable(sql, commandType, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            DataTable data = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                   
                    SqlDataAdapter adapter = new SqlDataAdapter(command);

                    adapter.Fill(data);
                }
            }
            return data;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, CommandType.Text, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, CommandType commandType)
        {
            return ExecuteReader(sql, commandType, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = new SqlCommand(sql, connection);

            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            connection.Open();
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public Object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, CommandType.Text, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public Object ExecuteScalar(string sql, CommandType commandType)
        {
            return ExecuteScalar(sql, commandType, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public Object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            object result = null;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    connection.Open();
                    result = command.ExecuteScalar();
                }
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, CommandType commandType)
        {
            return ExecuteNonQuery(sql, commandType, null);
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            int count = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = commandType;
                    
                    if (parameters != null)
                    {
                        foreach (SqlParameter parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    connection.Open();
                    count = command.ExecuteNonQuery();
                }
            }
            return count;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataTable GetTables()
        {
            DataTable data = null;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                data = connection.GetSchema("Tables");
            }
            return data;
        }

    }

    public class UserInfo
    {

        public int UserId { get; set; }
        public string UserName { get; set; }
        public string RealName { get; set; }
        public byte Age{ get; set; }
        public bool Sex { get; set; }
        public string Mobile { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }

    }

    public class Userinfo
    {
        public string username { get; set; }
        public string fullname { get; set; }
        public string password { get; set; }
        public int permission { get; set; }
        public string groupname { get; set; }
        public bool auto { get; set; }
        public string email { get; set; }
    }

    public class ADODotNetCRUD
    {
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int Count()
        {
            string sql = "select count(1) from UserInfo";
            SqlDbHelper db = new SqlDbHelper();
            return int.Parse(db.ExecuteScalar(sql).ToString());
        }


        public Userinfo ValidateUser(string username, string password)
        {
            string sql = "select * from Users Where username='" + username + "' and password='" + password + "'" ;
            SqlDbHelper db = new SqlDbHelper();
            DataTable data = db.ExecuteDataTable(sql);
            if (data.Rows.Count > 0)
            {
                DataRow row = data.Rows[0];
                Userinfo info = new Userinfo()
                {
                    username = row["UserName"].ToString(),
                    fullname = row["FullName"].ToString(),
                    password = row["Password"].ToString(),
                    permission = Convert.ToInt16(row["Permission"].ToString()),
                    groupname = row["GroupName"].ToString(),
                    auto = Convert.ToBoolean(row["Auto"])
                };
                return info;
            }
            else
            {
                return null;
            }
        }

        public Userinfo GetUser(string username)
        {
            string sql = "select * from Users Where username='" + username + "'";
            SqlDbHelper db = new SqlDbHelper();
            DataTable data = db.ExecuteDataTable(sql);
            if (data.Rows.Count > 0)
            {
                DataRow row = data.Rows[0];
                Userinfo info = new Userinfo()
                {
                    username = row["UserName"].ToString(),
                    fullname = row["FullName"].ToString(),
                    password = row["Password"].ToString(),
                    permission = Convert.ToInt16(row["Permission"].ToString()),
                    groupname = row["GroupName"].ToString(),
                    auto = Convert.ToBoolean(row["Auto"]),
                    email = row["Email"].ToString()
                };
                return info;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Create(UserInfo info)
        {
            string sql = "insert UserInfo(UserName,RealName,Age,Sex,Mobile,Email,Phone)values(@UserName,@RealName,@Age,@Sex,@Mobile,@Email,@Phone)";
            SqlParameter[] paramters = new SqlParameter[]{
                new SqlParameter("@UserName",info.UserName),
                new SqlParameter("@RealName",info.RealName),
                new SqlParameter("@Age",info.Age),
                new SqlParameter("@Sex",info.Sex),
                new SqlParameter("@Mobile",info.Mobile),
                new SqlParameter("@Email",info.Email),
                new SqlParameter("@Phone",info.Phone),
            };
            SqlDbHelper db = new SqlDbHelper();
            return db.ExecuteNonQuery(sql, CommandType.Text, paramters) > 0;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public UserInfo Read(int userId)
        {
            string sql = "select * from UserInfo Where UserId=" + userId;
            SqlDbHelper db = new SqlDbHelper();
            DataTable data = db.ExecuteDataTable(sql);
            if (data.Rows.Count > 0)
            {
                DataRow row = data.Rows[0];
                UserInfo info = new UserInfo()
                {
                    UserId = int.Parse(row["UserId"].ToString()),
                    UserName = row["UserName"].ToString(),
                    Age = byte.Parse(row["Age"].ToString()),
                    Email = row["Email"].ToString(),
                    Mobile = row["Mobile"].ToString(),
                    Phone = row["Phone"].ToString(),
                    RealName = row["RealName"].ToString(),
                    Sex = bool.Parse(row["Sex"].ToString())
                };
                return info;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool Update(UserInfo info)
        {
            string sql = "update UserInfo set UserName=@UserName,RealName=@RealName,Age=@Age,Sex=@Sex,Mobile=@Mobile,Email=@Email,Phone=@Phone where UserID=@UserID";
            SqlParameter[] paramters = new SqlParameter[]{
                new SqlParameter("@UserName",info.UserName),
                new SqlParameter("@RealName",info.RealName),
                new SqlParameter("@Age",info.Age),
                new SqlParameter("@Sex",info.Sex),
                new SqlParameter("@Mobile",info.Mobile),
                new SqlParameter("@Email",info.Email),
                new SqlParameter("@Phone",info.Phone),
                new SqlParameter("@UserID",info.UserId),
            };
            SqlDbHelper db = new SqlDbHelper();
            return db.ExecuteNonQuery(sql, CommandType.Text, paramters) > 0;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public bool Delete(int userId)
        {
            string sql = "delete from UserInfo where UserId=" + userId;
            SqlDbHelper db = new SqlDbHelper();
            return db.ExecuteNonQuery(sql) > 0;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int GetMaxUserId()
        {
            string sql = "select max(userId) from UserInfo";
            SqlDbHelper db = new SqlDbHelper();
            return int.Parse(db.ExecuteScalar(sql).ToString());
        }

        public string GetTotalRecord()
        {
            string sql = "select Count(userId) from tblUser";
            SqlDbHelper db = new SqlDbHelper();
            return db.ExecuteScalar(sql).ToString();
        }

        public string GetPartnerRecord(string PromotionCode)
        {
            string sql = "select Count(userId) from tblUser where PromotionCode='" + PromotionCode + "'";
            SqlDbHelper db = new SqlDbHelper();
            return db.ExecuteScalar(sql).ToString();
        }

        public string GetTotalCIRecord()
        {
            string sql = "select Count(userId) from tblCI";
            SqlDbHelper db = new SqlDbHelper();
            return db.ExecuteScalar(sql).ToString();
        }
    }

}